Questions: Average buying price

In Sage 200, the average buying price is updated each time goods are confirmed as received in Sage 200 with a known cost price.

The average buying price calculation is based on existing stock level when goods are received. It's calculated in the following way:

((Current Total Qty in Stock * Current Average Buying Price) + (Quantity Brought In * Price Brought In) )/ (Current Total Qty in Stock + Quantity Brought In).

For example:

1. You buy three items at £10. The average buying price is £10.
2. You sell one item. The average price is still £10 as the average price isn't affected by outgoing (sold) stock.

3. You buy a third item at £16. The average buying price is becomes £12.

   No in stock		Av price		No In		Price In		Total in stock
   ( ( 2	*	10 )	+	( 1	*	16) )	/	3

4. You buy a fourth item at £8. The average buying price is now £11.

   No in stock		Av price		No In		Price In		Total in stock
   ( ( 3	*	12 )	+	( 1	*	8 ) )	/	4

When the stock level of an item is zero (you have no outstanding stock), the cost of any new stock bought effectively becomes the new average buying price.

For example:

1. Your buy one item at £10. The average buying price is £10.
2. You sell this item, so your stock level is 0. The average price is still £10 as the average price isn't affected by outgoing (sold) stock.

3. You buy one item at £16. The average buying price is now £16.

   No in stock		Av price		No In		Price In		Total in stock
   ( ( 0	*	10 )	+	( 1	*	16 ) )	/	1

The average price isn't updated when the stock being brought in fulfils a shortfall, either fully or in part. This can occur when

• You allow negative stock.
• Unconfirmed stock has been despatched.

For example:

Your stock item allows negative stock.

1. You buy one item at £10. The average buying price is £10.
2. You sell this item. The average price is still £10 as the average price isn't affected by outgoing (sold) stock.
3. You sell a further two items making your stock level -2. The average buying price is still £10.
4. You buy one item at £15, making your stock level -1. The average price is still £10 because your stock level still negative.

You can choose to update cost prices when the goods are recorded as received in Sage 200 rather than when the purchase invoice is entered (Purchase Order Processing settings / Order Processing tab). This will ensure that your average buying price reflects the purchase cost of all items received.

Any differences in the price on the purchase order and the price on the purchase invoice are posted to the nominal account specified in the Purchase Order Processing settings.

The stock valuation report and the Nominal Ledger for items using the average cost method

The following example shows how the stock valuation and stock nominal ledger values are calculated for stock item that use the average costing method.

1. You buy one item at £10 and record the invoice.

   	No of Items	Average Buying Price	Total
   Stock Valuation	1	£10	£10
   Nominal Ledger	1	£10	£10

2. Buy another item at £20 and record the invoice.

   	No of Items	Average Buying Price	Total
   Stock Valuation	2	£15	£30
   Nominal Ledger Stock Account	2	£15	£30

3. You sell one of the items.

   	No of Items	Average Buying Price	Total
   Stock Valuation	1	£15	£15
   Nominal Ledger Stock Account	1	£15	£15
   Nominal Ledger Issues Account	1	£15	£15

4. You sell the other item.

   	No of Items	Average Buying Price	Total
   Stock Valuation	0	£15	0
   Nominal Ledger Stock Account	0	£15	0
   Nominal Ledger Issues Account	2	£15	£30

If you choose to update cost prices when the invoice is recorded, the cost price for items that use the average costing method will be based on the invoices that you have already received.

If you receive and sell goods before entering the purchase invoice, the cost price for items sold will be based on the invoices already recorded.

Remember, if you choose to update stock when the invoice is recorded, stock received is classed as Unconfirmed until the invoice is recorded. If you then sell the Unconfirmed stock, Sage 200 doesn't know the price that you paid for the stock and you create a shortfall. When you eventually post the POP invoice, the average buying price is only updated if the quantity invoiced is more than the shortfall. If the quantity invoiced only is less than or the same as the shortfall, then the average cost price is not affected.

The stock valuation report and the Nominal Ledger for stock items using the average cost method

The stock valuation report and the nominal ledger may show discrepancies when the cost prices is not updated until the invoice is entered. This is because stock can be issued before a confirmed price is entered.

The following example uses exaggerated values to illustrate this point.

1. You buy one item at £10 and record the invoice.

   	No of Items	Average Buying Price	Total
   Stock Valuation	1 confirmed	£10	£10
   Nominal Ledger	1	£10	£10

2. Buy another item and issue both items before the invoice is recorded. You have 1 confirmed and 1 unconfirmed item.

   	No of Items	Average Buying Price	Total
   Stock Valuation	-1 confirmed	£10	-£10
   Nominal Ledger Stock Account	-1	£10	-£10
   Nominal Ledger Issues Account	2	£10	£20

3. Record the invoice for the second item at £20. This item is now confirmed.

   The nominal ledger values are not adjusted for items that have already been issued.

   	No of Items	Average Buying Price	Total
   Stock Valuation	0 confirmed	£15	0
   Nominal Ledger Stock Account	1	£10	£10
   Nominal Ledger Issues Account	2	£10	£20

To retrospectively calculate the average buying price would be complex and generate a large number of adjusting transactions that would not be practical in real world situations.

To monitor these discrepancies, we recommend that you create different nominal accounts for stock items that use the average costing method. Ideally, each product group should be linked to its own nominal account. In this way, you would be able to compare the stock valuation for the items in the product group with its associated nominal account and make the required adjustments.

If you have chosen to use the 'Average' costing method for your stock items, the average buying price is used to calculate:

• The cost of sales from your sales orders.
• The value of your stock.
• The transactions posted to your stock nominal accounts.

Here are some examples of how the average buying price is used in Sage 200:

• Sales order profit.

  The estimated cost shown for sales order profit calculations is the average buying price for stock items using the 'FIFO', 'Actual' or 'Average' costing methods.

  You can change this if you have the correct user permissions

• Stock valuation.

  Stock items that use the 'Average' costing method are valued using the average buying price for that item.

• Cost of sales.

  Cost of sales transactions are calculated using the average buying price, for stock items that use the 'Average' costing method.

• Works Orders (Sage 200 Manufacturing).

  When you issue a stock item that uses the 'Average' costing method to a works order, the average buying price is used to update the works order costs.

• Estimating (Sage 200 Manufacturing).

  When entering estimates, the average buying price is used as the cost price of component stock items that use the 'FIFO', 'Average' and 'Actual' costing methods unless you have a preferred supplier for that component in which case the latest last or list price of the component will be used.

• Bill of Materials.

  The estimated cost of a BOM is calculated using the average buying price for stock items that use the 'FIFO', 'Average', and 'Actual' costing methods.